package SQL;

import MySQLConnection.Conn;

import java.sql.*;

public class SQLTest {
    /*批量查询*/
    public void select() {
        try (Connection con = new Conn().getConnection()) {
            /*关闭自动提交*/
            con.setAutoCommit(false);
            long lastId = 0;
            int batchSize = 50000;
            boolean hasMore = true;
            while (hasMore) {
                String selectSql = "select id,username,password from user where id > ? order by id limit ?";
                try (PreparedStatement pr = con.prepareStatement(selectSql)) {
                    pr.setLong(1, lastId);
                    pr.setLong(2, batchSize);
                    ResultSet rs = pr.executeQuery();
                    int count = 0;
                    while (rs.next()) {
                        System.out.println("id: " + rs.getLong(1) + " username: " +
                                rs.getString(2) +
                                " password: " + rs.getString(3));
                        lastId = rs.getLong("id");
                        count++;
                    }
                    if (count > 0) {
                        System.out.println(count);
                    } else {
                        hasMore = false;
                    }
                } catch (SQLException e) {
                    System.out.println("SQL异常！");
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            System.out.println("连接异常！");
        }
    }

    /*批量进行插入操作*/
    public void insert() {
        int batchSize = 50000;
        Conn conn = new Conn();
        Connection con = conn.getConnection();
        String sql = "insert into user(id,username,password) values(?,?,?)";
        try (PreparedStatement ps = con.prepareStatement(sql)) {
            con.setAutoCommit(false);
            for (long i = 0; i < 5000000; i++) {
                ps.setLong(1, i);
                ps.setString(2, "user" + i);
                ps.setString(3, "password" + i);
                ps.addBatch();
                if (i % batchSize == 0) {
                    System.out.println("开始执行插入");
                    ps.executeBatch();
                    con.commit();
                    ps.clearBatch();
                }
            }
            /*执行剩下的语句*/
            ps.executeBatch();
            con.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insert_select_user_user1(long size) {
        try (Connection con = new Conn().getConnection()) {
            /*关闭自动提交*/
            con.setAutoCommit(false);
            long lastId = 0;
            long batchSize = size;
            boolean hasMore = true;
            while (hasMore) {
                String selectSql = "select id,username,password from user where id > ? order by id limit ?";
                try (PreparedStatement pr = con.prepareStatement(selectSql)) {
                    pr.setLong(1, lastId);
                    pr.setLong(2, batchSize);
                    ResultSet rs = pr.executeQuery();
                    System.out.println(batchSize + "条数据查询成功！");
                    int count = 0;
                    String insertSql = "INSERT INTO user1 (id, username, password) VALUES (?, ?, ?)";
                    try (PreparedStatement psInsert = con.prepareStatement(insertSql)) {
                        while (rs.next()) {
                            psInsert.setLong(1, rs.getLong("id"));
                            psInsert.setString(2, rs.getString("username"));
                            psInsert.setString(3, rs.getString("password"));
                            psInsert.addBatch();
                            lastId = rs.getLong("id");
                            count++;
                        }
                        if (count > 0) {
                            System.out.println("插入执行ing...");
                            psInsert.executeBatch();
                            con.commit();
                            System.out.println("提交完毕!!!");
                        } else {
                            hasMore = false;
                        }
                    }
                } catch (SQLException e) {
                    System.out.println("SQL异常！");
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            System.out.println("连接异常！");
        }
    }

    public void insert_select(long size) {
        try (Connection con = new Conn().getConnection()) {
            /*关闭自动提交*/
            con.setAutoCommit(false);
            long lastId = -1;
            long batchSize = size;
            boolean hasMore = true;
            String insertSql = "INSERT INTO user1 (id, username, password)"+"select id,username,password from user where id > ? order by id limit ?";
            while (hasMore) {
                try (PreparedStatement pr = con.prepareStatement(insertSql)) {
                    pr.setLong(1, lastId);
                    pr.setLong(2, batchSize);
                    int rows = pr.executeUpdate();
                    if(rows > 0) {
                        con.commit();
                        System.out.println("已插入"+rows+"条数据");
                        try(Statement st = con.createStatement();
                        ResultSet rs = st.executeQuery("select max(id) from user1")) {
                            if(rs.next()) {
                                lastId = rs.getLong(1);
                            }
                        }
                    }else {
                        hasMore = false;
                        System.out.println("插入成功！");
                    }
                } catch (SQLException e) {
                    System.out.println("SQL异常！");
                    //异常回滚
                    con.rollback();
                    e.printStackTrace();
                }
            }
        } catch (
                SQLException e) {
            System.out.println("连接异常！");
        }
    }

}
